/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Delphine
 */
public class Session_diplome {
    private int id_session;
    private int id_version_diplome;
    private String id_formation;
    private String nom;
    private String date_debut;
    private String date_fin;
    private Formateur responsable;
    private String id_annee_session;
    private ArrayList<Integer> projetsID;

    /**
     *
     * @param id_session
     * @param id_version_diplome
     * @param id_formation
     * @param nom
     * @param date_debut
     * @param date_fin
     * @param responsable
     * @param id_annee_session
     * @param projetsID
     */
    public Session_diplome(int id_session, int id_version_diplome, String id_formation, String nom, String date_debut, String date_fin, Formateur responsable, String id_annee_session,  ArrayList<Integer> projetsID) {
        this.id_session = id_session;
        this.id_version_diplome = id_version_diplome;
        this.id_formation = id_formation;
        this.nom = nom;
        this.date_debut = date_debut;
        this.date_fin = date_fin;
        this.responsable = responsable;
        this.id_annee_session = id_annee_session;
        this.projetsID = projetsID;
    }

    /**
     *
     * @return
     */
    public String getId_annee_session() {
        return id_annee_session;
    }

    /**
     *
     * @return
     */
    public String getDate_debut() {
        return date_debut;
    }

    /**
     *
     * @return
     */
    public String getDate_fin() {
        return date_fin;
    }

    /**
     *
     * @return
     */
    public String getId_formation() {
        return id_formation;
    }

    /**
     *
     * @return
     */
    public int getId_session() {
        return id_session;
    }

    /**
     *
     * @return
     */
    public int getId_version_diplome() {
        return id_version_diplome;
    }

    /**
     *
     * @return
     */
    public String getNom() {
        return nom;
    }

    /**
     * 
     * @return
     */
    public Formateur getResponsable() {
        return responsable;
    }

    /**
     *
     * @return
     */
    public ArrayList<Integer> getProjetsID() {
        return projetsID;
    }



     /** Obtenir la session_diplome d'id id_session. Null si pas trouvé.
   *
   * @param id_session
   * @return
   * @throws java.sql.SQLException
   */
    public static Session_diplome getById(int id_session) throws SQLException{
        Session_diplome result = null;
        Connection connexion = null;
        Statement select = null;
        ResultSet rs = null;
        try {
          connexion = BD.connect();
          select = connexion.createStatement();
          String sql = "SELECT * FROM session_diplome WHERE id_session='"+ id_session+"'";
          rs = select.executeQuery(sql);
          if (rs.next()) {
            result = new Session_diplome(rs.getInt("id_session"),rs.getInt("id_version_diplome"),rs.getString("id_formation"),rs.getString("nom"),(""+rs.getTimestamp("date_debut")).substring(0, 10),(""+rs.getTimestamp("date_fin")).substring(0, 10), Formateur.getById(rs.getInt("id_session")), rs.getString("id_annee_session"), getProjetsID(rs.getInt("id_session")));
          }
          System.out.println(result);
        } finally {
            BD.close(rs);
            BD.close(select);
            BD.close(connexion);
        }
        return result;
    }

    /** Obtenir le libellé correspondant à l'id_version_diplome de cette Session. Null si pas trouvé.
   *
     * @param id_version_diplome
   * @return
   * @throws java.sql.SQLException
   */
    public static String getVersionDiplome(int id_version_diplome) throws SQLException{
        Connection connexion = null;
        Statement select = null;
        ResultSet rs = null;
        String libelle = null;
        try {
          connexion = BD.connect();
          select = connexion.createStatement();
          String sql = "SELECT libelle FROM version_diplome WHERE id_version_diplome ='" + id_version_diplome +"'";
          rs = select.executeQuery(sql);
          if (rs.next()) {
            libelle = rs.getString("libelle");
          }
        } finally {
            BD.close(rs);
            BD.close(select);
            BD.close(connexion);
        }
        return libelle;
    }

   /** Obtenir le libellé correspondant à l'id_formation de cette Session. Null si pas trouvé.
   *
    * @param id_formation
   * @return
   * @throws java.sql.SQLException
   */
    public static String getFormation(String id_formation) throws SQLException{
        Connection connexion = null;
        Statement select = null;
        ResultSet rs = null;
        String libelle = null;
        try {
          connexion = BD.connect();
          select = connexion.createStatement();
          String sql = "SELECT libelle FROM formation WHERE id_formation = '" + id_formation + "'";
          rs = select.executeQuery(sql);
          if (rs.next()) {
            libelle = rs.getString("libelle");
          }
        } finally {
            BD.close(rs);
            BD.close(select);
            BD.close(connexion);
        }
        return libelle;
    }

    /** Obtenir les ID des projets li�s a la session. Null si pas trouv�.
   *
     * @param id_session
   * @return
   * @throws java.sql.SQLException
   */
    public static ArrayList<Integer> getProjetsID(int id_session) throws SQLException{
        Connection connexion = null;
        Statement select = null;
        ResultSet rs = null;
        ArrayList<Integer> projets = null;
        try {
          connexion = BD.connect();
          select = connexion.createStatement();
          String sql =
                  "SELECT P.id_projet FROM PROJET P WHERE P.id_session = '" + id_session + "'";
          rs = select.executeQuery(sql);
          projets = new ArrayList<Integer>();
          while (rs.next() != false) {
           projets.add(rs.getInt("id_projet"));
          }
        } finally {
            BD.close(rs);
            BD.close(select);
            BD.close(connexion);
        }
        return projets;
    }

  /** Obtenir les groupes li�s au projet. Null si pas trouv�.
   *
   * @param id_session
   * @return
   * @throws java.sql.SQLException
   */
    public static ArrayList<Projet> getProjets(int id_session) throws SQLException{
        ArrayList<Integer> projetsID = getProjetsID(id_session);
        ArrayList<Projet> projets = new ArrayList<Projet>();
        for(int i =0; i<projetsID.size(); i++){
            projets.add(Projet.getById(projetsID.get(i)));
        }
        return projets;
    }

    @Override
    public boolean equals(Object session_diplome){
        return (id_session==((Session_diplome)session_diplome).getId_session()
                &&id_version_diplome==((Session_diplome)session_diplome).getId_version_diplome()
                &&id_formation.equals(((Session_diplome)session_diplome).getId_formation())
                &&nom.equals(((Session_diplome)session_diplome).getNom())
                &&date_debut.equals(((Session_diplome)session_diplome).getDate_debut())
                &&date_fin.equals(((Session_diplome)session_diplome).getDate_fin())
//                &&responsable.equals(((Session_diplome)session_diplome).getResponsable())
                &&id_annee_session.equals(((Session_diplome)session_diplome).getId_annee_session()));
    }

}
